package com.scuec.groupfive.mapper;

import com.scuec.groupfive.vo.DailyPracticesVO;
import com.scuec.groupfive.vo.AnswerResultVO;
import org.apache.ibatis.annotations.*;
import java.util.List;
import java.util.Map;

@Mapper
public interface DailyPracticesMapper {

    @Select({
            "SELECT ",
            "    q.question_id,",
            "    s.subject_name AS subject,",
            "    IFNULL(et.exam_type, '通用') AS exam_type,",
            "    q.content AS question_content,",
            "    q.question_type,",
            "    CASE ",
            "        WHEN q.question_type = 'single_choice' THEN ",
            "            (SELECT ",
            "                JSON_EXTRACT(cq.options, '$') ", // 直接提取整个JSON对象
            "             FROM choice_questions cq ",
            "             WHERE cq.question_id = q.question_id)",
            "        ELSE NULL",
            "    END AS choices",
            "FROM questions q",
            "JOIN subjects s ON q.subject_id = s.subject_id",
            "LEFT JOIN exam_types et ON q.type_id = et.type_id",
            "LEFT JOIN choice_questions cq ON q.question_id = cq.question_id",
            "WHERE q.question_id IN (",
            "    SELECT tq.question_id ",
            "    FROM daily_tasks dt",
            "    JOIN task_questions tq ON dt.task_id = tq.task_id",
            "    WHERE dt.user_id = #{userId} ",
            "      AND dt.task_date = CURRENT_DATE() ",
            "      AND dt.status = 'pending'",
            ") ",
            "ORDER BY ",
            "    FIELD(s.subject_name, '高等数学', '线性代数', '概率论与数理统计'),",
            "    FIELD(q.question_type, 'single_choice', 'fill_in_the_blank', 'subjective'),",
            "    q.question_id"
    })
    List<DailyPracticesVO> getDailyPracticesByUserId(@Param("userId") Long userId);

    @Select({
            "<script>",
            "SELECT ",
            "    q.question_id,",
            "    s.subject_name AS subject,",
            "    IFNULL(et.exam_type, '通用') AS exam_type,",
            "    q.content AS question_content,",
            "    q.question_type,",
            "    CASE ",
            "        WHEN q.question_type = 'single_choice' THEN cq.answer",
            "        WHEN q.question_type = 'fill_in_the_blank' THEN fb.answer_template",
            "        WHEN q.question_type = 'subjective' THEN sq.reference_answer",
            "        ELSE NULL",
            "    END AS correct_answer,",
            "    CASE ",
            "        WHEN q.question_type = 'single_choice' THEN cq.analysis",
            "        WHEN q.question_type = 'fill_in_the_blank' THEN fb.analysis",
            "        WHEN q.question_type = 'subjective' THEN sq.analysis",
            "        ELSE NULL",
            "    END AS analysis",
            "FROM questions q",
            "JOIN subjects s ON q.subject_id = s.subject_id",
            "LEFT JOIN exam_types et ON q.type_id = et.type_id",
            "LEFT JOIN choice_questions cq ON q.question_id = cq.question_id AND q.question_type = 'single_choice'",
            "LEFT JOIN fill_blank_questions fb ON q.question_id = fb.question_id AND q.question_type = 'fill_in_the_blank'",
            "LEFT JOIN subjective_questions sq ON q.question_id = sq.question_id AND q.question_type = 'subjective'",
            "WHERE q.question_id IN ",
            "<foreach collection='questionIds' item='id' open='(' separator=',' close=')'>",
            "    #{id}",
            "</foreach>",
            "</script>"
    })
    List<AnswerResultVO> getCorrectAnswers(@Param("questionIds") Long[] questionIds);

    @Insert({
            "INSERT INTO answer_records (user_id, question_id, question_type, answer, is_correct, time_spent, answered_at, subject_id)",
            "VALUES (#{userId}, #{questionId}, #{questionType}, #{answer}, #{isCorrect}, #{timeSpent}, NOW(), ",
            "(SELECT subject_id FROM subjects WHERE subject_name = #{subject}))"
    })
    void insertAnswerRecord(
            @Param("userId") Long userId,
            @Param("questionId") Long questionId,
            @Param("questionType") String questionType,
            @Param("answer") String answer,
            @Param("isCorrect") Boolean isCorrect,
            @Param("timeSpent") Integer timeSpent,
            @Param("subject") String subject
    );

    @Insert({
            "INSERT INTO wrong_questions (user_id, question_id, wrong_count, last_wrong_time, is_favorite, subject_id)",
            "VALUES (#{userId}, #{questionId}, 1, NOW(), 0, ",
            "(SELECT subject_id FROM subjects WHERE subject_name = #{subject}))",
            "ON DUPLICATE KEY UPDATE wrong_count = wrong_count + 1, last_wrong_time = NOW()"
    })
    void insertWrongQuestion(
            @Param("userId") Long userId,
            @Param("questionId") Long questionId,
            @Param("subject") String subject
    );

    @Update({
            "UPDATE daily_tasks SET status = 'completed', updated_at = NOW()",
            "WHERE user_id = #{userId} AND task_date = CURRENT_DATE() AND status = 'pending'"
    })
    void updateTaskStatus(@Param("userId") Long userId);

    @Select("<script>" +
            "SELECT practice_date, " +
            "CONCAT('daily_', practice_date) AS practice_id, " +
            "SUM(total_time) AS total_time, " +  // 确保这里是SUM
            "MAX(created_at) AS created_at, " +
            "COUNT(*) AS total_count, " +
            "SUM(CASE WHEN is_correct = 1 THEN 1 ELSE 0 END) AS correct_count, " +
            "GROUP_CONCAT(DISTINCT subject_name) AS subjects " +
            "FROM ( " +
            "  SELECT " +
            "    DATE(ar.answered_at) AS practice_date, " +
            "    ar.time_spent AS total_time, " +
            "    ar.answered_at AS created_at, " +
            "    s.subject_name, " +
            "    ar.is_correct " +
            "  FROM answer_records ar " +
            "  JOIN subjects s ON ar.subject_id = s.subject_id " +
            "  WHERE ar.user_id = #{userId} " +
            "    AND ar.question_type IN ('single_choice', 'fill_in_the_blank', 'subjective') " +
            "    <if test='startDate != null and startDate != \"\"'> AND DATE(ar.answered_at) &gt;= #{startDate} </if>" +
            "    <if test='endDate != null and endDate != \"\"'> AND DATE(ar.answered_at) &lt;= #{endDate} </if>" +
            "    <if test='keyword != null and keyword != \"\"'> AND (s.subject_name LIKE CONCAT('%', #{keyword}, '%') " +
            "        OR ar.answer LIKE CONCAT('%', #{keyword}, '%')) </if> " +
            ") t " +
            "GROUP BY practice_date " +
            "ORDER BY practice_date DESC " +
            "LIMIT #{limit} OFFSET #{offset}" +
            "</script>")
    List<Map<String, Object>> getDailyPracticeRecords(
            @Param("userId") Long userId,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate,
            @Param("keyword") String keyword,
            @Param("offset") int offset,
            @Param("limit") int limit);

    @Select({
            "SELECT ",
            "  ar.record_id, ",
            "  ar.is_favorite, ",
            "  q.question_id, ",
            "  s.subject_name AS subject, ",
            "  IFNULL(et.exam_type, '通用') AS exam_type, ",
            "  q.content AS question_content, ",
            "  q.question_type, ",
            "  ar.answer AS user_answer, ",
            "  ar.is_correct, ",
            "  ar.time_spent, ",
            "  CASE ",
            "    WHEN q.question_type = 'single_choice' THEN cq.answer ",
            "    WHEN q.question_type = 'fill_in_the_blank' THEN fb.answer_template ",
            "    WHEN q.question_type = 'subjective' THEN sq.reference_answer ",
            "    ELSE NULL ",
            "  END AS correct_answer, ",
            "  CASE ",
            "    WHEN q.question_type = 'single_choice' THEN cq.analysis ",
            "    WHEN q.question_type = 'fill_in_the_blank' THEN fb.analysis ",
            "    WHEN q.question_type = 'subjective' THEN sq.analysis ",
            "    ELSE NULL ",
            "  END AS analysis, ",
            "  CASE ",
            "    WHEN q.question_type = 'single_choice' THEN ",
            "      (SELECT ",
            "          GROUP_CONCAT(",
            "            CONCAT(kv.key_char, '. ', JSON_UNQUOTE(JSON_EXTRACT(cq.options, CONCAT('$.\"', kv.key_char, '\"'))) )",
            "            SEPARATOR ',' ",
            "          ) ",
            "       FROM (SELECT 'A' AS key_char UNION SELECT 'B' UNION SELECT 'C' UNION SELECT 'D') kv ",
            "       WHERE JSON_EXTRACT(cq.options, CONCAT('$.\"', kv.key_char, '\"')) IS NOT NULL ",
            "      ) ",
            "    ELSE NULL ",
            "  END AS choices ",
            "FROM answer_records ar ",
            "JOIN questions q ON ar.question_id = q.question_id ",
            "JOIN subjects s ON q.subject_id = s.subject_id ",
            "LEFT JOIN exam_types et ON q.type_id = et.type_id ",
            "LEFT JOIN choice_questions cq ON q.question_id = cq.question_id AND q.question_type = 'single_choice' ",
            "LEFT JOIN fill_blank_questions fb ON q.question_id = fb.question_id AND q.question_type = 'fill_in_the_blank' ",
            "LEFT JOIN subjective_questions sq ON q.question_id = sq.question_id AND q.question_type = 'subjective' ",
            "WHERE ar.user_id = #{userId} ",
            "  AND DATE(ar.answered_at) = #{practiceDate} ",
            "  AND ar.question_type IN ('single_choice', 'fill_in_the_blank', 'subjective') ",
            "ORDER BY ar.answered_at"
    })
    List<AnswerResultVO> getPracticeResults(
            @Param("userId") Long userId,
            @Param("practiceDate") String practiceDate);

    /**
     * 更新答题记录的收藏状态
     * @param recordId 答题记录ID
     * @param isFavorite 是否收藏(0-未收藏,1-已收藏)
     * @return 影响的行数
     */
    @Update("UPDATE answer_records SET is_favorite = #{isFavorite} WHERE record_id = #{recordId}")
    int updateFavoriteStatus(
            @Param("recordId") Long recordId,
            @Param("isFavorite") int isFavorite
    );

    /**
     * 添加题目到收藏夹（如果不存在）
     * @param userId 用户ID
     * @param questionId 题目ID
     * @return 影响的行数
     */
    @Insert({
            "INSERT INTO favorites (user_id, question_id, status, created_at, updated_at)",
            "VALUES (#{userId}, #{questionId}, 1, NOW(), NOW())",
            "ON DUPLICATE KEY UPDATE status = 1, updated_at = NOW()"
    })
    int addToFavorites(
            @Param("userId") Long userId,
            @Param("questionId") Long questionId
    );
    @Update("UPDATE favorites SET status = 0, updated_at = NOW() WHERE user_id = #{userId} AND question_id = #{questionId}")
    int removeFromFavorites(
            @Param("userId") Long userId,
            @Param("questionId") Long questionId
    );
}